namespace CRMG.Container.Source.QO
{
    /* Query Object Class */
    public class Crud
    {
        #region [ Student Crud ]

        public static string IsStudentExist()
        {
            return "Select Count(*) From Student Where FirstName=@FirstName And LastName=@LastName;";
        }

        public static string GetStudentList()
        {
            return @"Select St.FirstName,St.LastName,St.FirstName+' '+St.LastName As FullName,St.EMail,
                    St.Phone,St.MobilePhone,C.CompanyName,St.OBJID,St.IsActive From Student St
                    Left Join Company C on St.CompanyId=C.OBJID Order By FirstName;";
        }

        public static string GetAllStudents()
        {
            return "Select OBJID,FirstName+' '+LastName As Name From Student;";
        }

        public static string GetActiveStudentList()
        {
            return "Select OBJID,FirstName+' '+LastName As Name From Student Where IsActive=1;";
        }

        #endregion


        public static string GetStudentTrains()
        {
            return @"Select ST.AvailableTrainId, ST.TrainTypeId From StudentTrain ST
                    Inner Join AvailableTrain AT On ST.AvailableTrainId=AT.OBJID
                    Where DATEDIFF(DAY,GETDATE(),AT.EndTime)<0 And ST.StudentId=@StudentId;";
        }

        public static string GetAvailableTrains()
        {
            return @"Select AT.OBJID,T.TrainName From AvailableTrain AT 
                    Inner Join Train T On AT.TrainId=T.OBJID
                    Where DATEDIFF(DAY,EndTime,GetDate()) > 0;";
        }

        internal static string GetAvailableTrainsOfStudent()
        {
            return @"Select AT.*,ST.TrainTypeId,ST.TrainTypeId From AvailableTrain AT
                    Left Join StudentTrain ST On AT.OBJID=ST.StudentId
                    Where ST.StudentId=@StudentId;";
        }

        public static string GetInterViews()
        {
            return @"Select U.FirstName+' '+U.LastName As UserFullName,
                    S.FirstName+' '+S.LastName As StudentFullName,
                    I.InterViewTime, I.Remarks, I.Comments,I.OBJID,I.UserId,I.StudentId
                    From Interview I
                    Inner Join Users U on I.UserId = U.OBJID
                    Inner Join Student S on I.StudentId=S.OBJID;";
        }

        public static string GetInterViewsOfStudent()
        {
            return @"Select U.FirstName+' '+U.LastName As UserFullName,
                    S.FirstName+' '+S.LastName As StudentFullName,
                    Convert(NVarChar, I.InterViewTime) As InterViewTime, I.Remarks, I.Comments,I.OBJID,I.UserId,I.StudentId
                    From Interview I
                    Inner Join Users U on I.UserId = U.OBJID
                    Inner Join Student S on I.StudentId=S.OBJID
                    Where I.StudentId=@StudentId;";
        }

        public static string GetPayments()
        {
            return @"Select S.FirstName+' '+S.LastName As StudentFullName,
					U.FirstName+' '+U.LastName As UserFullName,
                    I.Amount, I.PaymentTime, PT.PaymentTypeName,I.OBJID,I.UserId,I.StudentId
                    From InPayment I
                    Inner Join Users U on I.UserId = U.OBJID
                    Inner Join Student S on I.StudentId=S.OBJID
					Inner Join PaymentType PT on I.PaymentType=PT.OBJID ;";
        }

        internal static string GetDetailsOfStudent()
        {
            return "Select FirstName+' '+LastName as FullName,Phone,MobilePhone,EMail,WorkEMail,Address,Resim From Student Where OBJID=@StudentId;";
        }
    }
}
